﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;
using Kaidou.Model;


namespace Kaidou.Persister
{
    public class TRN_Report_H_Persister: Common
    {

        /**
	     * サーチモデルに指定された条件でTRN_Report_HテーブルからTRN_Report_Hのリストを取得します。
	     * @param	con				DBコネクション
	     * @param	model			報告見出サーチモデル
	     * @return	Vector			報告見出モデルのリスト
	    */
        public Pager search(ReportSearchModel model, int pageNo, int pageSize) 
        {

            ArrayList list = new ArrayList();

            string sqlFrom ="";
            string sqlFromwhere="";
            if (StringUtil.isEmpty(model.getReport_sts()))
            {
                sqlFrom = "select top "+(pageNo*pageSize) +" upd_date as report_date_order,upd_date as report_date,seal_no as report_no, ";
					sqlFrom += "  '' as union_code, '' as branch_code, '' as hall_name, ";
					sqlFrom += "  '' as hall_pref_code, '' as hall_city, '' as hall_address, '' as hall_tel,  ";

                    bool machineclassflag = false;

                    if (!StringUtil.isEmpty(model.getMachine_clss()))
                    {
                        machineclassflag = true;

                    }
 					if (machineclassflag)
                    {
                        sqlFrom = sqlFrom + "  ''as machine_clss, ";
                    }

                    sqlFrom += "  '' as machine_name, trader_reg_no as trader_reg_no, '' as est_date, 1 as machine_qty,  ";
                    sqlFrom += "  '' as report_clss, purch_sts as report_sts, '' as fax_error_flg, '' as dup_error_flg, '' as dif_report_flg  ";

                    //sqlFromwhere += "  from trn_purch_seal as t1 					where (t1.purch_sts ='" + TrnPurchSeal_a.STATUS_NORMAL + "' or t1.purch_sts ='" + TrnPurchSeal_a.STATUS_CANSEL + "' or t1.purch_sts='" + TrnPurchSeal_a .STATUS_DENIAL+ "')";
                sqlFromwhere += "  from trn_purch_seal as t1 					where (t1.purch_sts ='" + TrnPurchSeal_a.STATUS_NORMAL + "' or t1.purch_sts ='" + TrnPurchSeal_a.STATUS_CANSEL + "')";
				
			        //条件 開始　Report_date_str

                    if (!StringUtil.isEmpty(model.getReport_date_str()))
                    {

                        sqlFromwhere += " and CONVERT(varchar(20),t1.upd_date,112) >= '" + model.getReport_date_str() + "' ";

                    }

                    //条件 終了　Report_date_end

                    if (!StringUtil.isEmpty(model.getReport_date_end()))
                    {

                        sqlFromwhere += " and CONVERT(varchar(20),t1.upd_date,112) <= '" + model.getReport_date_end() + "' ";

                    }
                    

                    if (!StringUtil.isEmpty(model.getSeal_str_no()))
                    {
                        if (StringUtil.isEmpty(model.getSeal_end_no()))
                        {
                            sqlFromwhere += " and t1.seal_no = '" + model.getSeal_str_no() + "' ";
                        }
                        else
                        {
                            sqlFromwhere += " and t1.seal_no >= '" + model.getSeal_str_no() + "' ";
                        }
                        
                    }

                    //条件 シールNo.(end）

                    if (!StringUtil.isEmpty(model.getSeal_end_no()))
                    {
                        
                            sqlFromwhere += " and t1.seal_no <= '" + model.getSeal_end_no() + "' "; // end
                        

                    }

                    //条件 販売業者登録番号

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlFromwhere += " and t1.trader_reg_no like '" + model.getTrader_reg_no1() + "%'";

                    }

                    if (StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlFromwhere += " and t1.trader_reg_no like '%" + model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlFromwhere += " and t1.trader_reg_no = '" + model.getTrader_reg_no1() +"-"+ model.getTrader_reg_no2() + "'";

                    }
					
            }
    

            if (!StringUtil.isEmpty(model.getReport_sts()) && model.getReport_sts().Equals("888"))
            {
                SqlDB db = new SqlDB();
                try
                {

                    string sql = " a.purch_sts in('" + TrnPurchSeal_a.STATUS_NORMAL + "','"+TrnPurchSeal_a.STATUS_DENIAL+"')";

                    if(!StringUtil.isEmpty(model.getUnion_code())){
                        sql += " and a.union_code='"+model.getUnion_code()+"' ";
                    }

                    if (model.getBranch_code() >= 0)
                    {

                        sql += " and a.branch_code = " + model.getBranch_code();

                    }

                    //条件 開始　Report_date_str

                    if (!StringUtil.isEmpty(model.getReport_date_str()))
                    {

                        sql += "  and CONVERT(varchar(20),a.upd_date,112) >= '" + model.getReport_date_str() + "' ";

                    }

                    //条件 終了　Report_date_end

                    if (!StringUtil.isEmpty(model.getReport_date_end()))
                    {

                        sql += " and CONVERT(varchar(20),a.upd_date,112) <= '" + model.getReport_date_end() + "' ";

                    }

                    
                    if (!StringUtil.isEmpty(model.getSeal_str_no()))
                    {
                        if (StringUtil.isEmpty(model.getSeal_end_no()))
                        {
                            sql += " and a.seal_no = '" + model.getSeal_str_no() + "' ";
                        }
                        else
                        {

                            sql += " and a.seal_no >= '" + model.getSeal_str_no() + "' ";
                        }
                       
                    }
                    


                    //条件 シールNo.(end）

                    if (!StringUtil.isEmpty(model.getSeal_end_no()))
                    {
                        
                            sql += " and a.seal_no <= '" + model.getSeal_end_no() + "' "; // end
                       

                    }

                    //条件 販売業者登録番号

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no like '" + model.getTrader_reg_no1() + "%'";

                    }

                    if (StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no like '%" + model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no = '" + model.getTrader_reg_no1() +"-"+ model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getOffice_name()))
                    {
                        sql += " and b.office_name like '%" + model.getOffice_name() + "%' and b.branch_code=0 ";
                    }

                    if (!StringUtil.isEmpty(model.getOffice_kana()))
                    {
                        sql += " and b.office_kana like '%" + model.getOffice_kana() + "%' and b.branch_code=0 ";
                    }

                    if (!StringUtil.isEmpty(model.getBranch_name()))
                    {
                        sql += " and b.office_name like '%" + model.getOffice_name() + "%' and b.branch_code>0 ";
                    }

                    sql += " and a.union_code=b.union_code and a.branch_code=b.branch_code ";

                    string nsql = "select top " + (pageNo * pageSize) + " * from trn_purch_seal as a,mst_union_view as b where " + sql;

                    ReportSearchModel report = null;

                    LogManager.info("TRN_Report_HPersister search query : " + nsql);

                   
                    SqlParameter[] sqlParameter = { };
                    SqlDataReader dataReader = db.GetDataCmd(nsql, sqlParameter);

                    int machine_total_qty = 0;
                    while (dataReader.Read())
                    {

                        report = new ReportSearchModel();

                        report.setReport_date((DateTime)dataReader["upd_date"]);

                        report.setSeal_str_no((string)dataReader["seal_no"]);
                        report.setReport_no((string)dataReader["seal_no"]);

                        report.setUnion_code((string)dataReader["union_code"]);

                        report.setBranch_code((short)dataReader["branch_code"]);

                        report.setTrader_reg_no(((string)dataReader["trader_reg_no"]).Replace("-", ""));


                        report.setReport_sts((string)dataReader["purch_sts"]);
                       
                        report.setMachine_qty(1);
                        machine_total_qty = machine_total_qty + 1;
                        list.Add(report);

                    }

                    dataReader.Close();

                    int count = 0;

                    String pssql = "select count(*) from trn_purch_seal as a,mst_union_view as b  where " + sql;

                    LogManager.info("TRN_Report_HPersister search count query : " + pssql);
                    
                    
                    dataReader = db.GetDataCmd(pssql, sqlParameter);
                
                    if (dataReader.Read())
                    {
                        count = (int)dataReader[0];
                    }

                    if (pageNo == 0)
                    {
                        pageNo = 1;
                    }

                    Pager pager = new Pager();
                    pager.PageNo = pageNo;
                    pager.PageSize = pageSize;
                    pager.Total = count;
                    pager.Other = count.ToString();
                    pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
                    ArrayList result = new ArrayList();
                    object[] bsm = list.ToArray();
                    if (list.Count == 1)
                    {
                        pager.List = list;
                    }
                    else
                    {
                        //((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize))
                        for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
                        {
                            result.Add((ReportSearchModel)bsm[i]);

                        }
                        pager.List = result;

                    }
                   // pager.List = list;
                    
                    db.Close();

                    return pager;

                }catch(Exception e2){
                    db.Close();
                    LogManager.error("trn_report_h_persister "+e2.Message+e2.StackTrace);
                }




            }
            else if (model.getCansel())
            {
                SqlDB db = new SqlDB();
                try
                {
                    string sql = " a.purch_sts ='" + TrnPurchSeal_a.STATUS_CANSEL + "'";

                    if (!StringUtil.isEmpty(model.getUnion_code()))
                    {
                        sql += " and a.union_code='" + model.getUnion_code() + "' ";
                    }

                    if (model.getBranch_code() >= 0)
                    {

                        sql += " and a.branch_code = " + model.getBranch_code();

                    }

                    //条件 開始　Report_date_str

                    if (!StringUtil.isEmpty(model.getReport_date_str()))
                    {

                        sql += " and CONVERT(varchar(20),a.upd_date,112) >= '" + model.getReport_date_str() + "' ";

                    }

                    //条件 終了　Report_date_end

                    if (!StringUtil.isEmpty(model.getReport_date_end()))
                    {

                        sql += " and CONVERT(varchar(20),a.upd_date,112) <= '" + model.getReport_date_end() + "' ";

                    }


                    if (!StringUtil.isEmpty(model.getSeal_str_no()))
                    {

                        if (StringUtil.isEmpty(model.getSeal_end_no()))
                        {
                            sql += " and a.seal_no = '" + model.getSeal_str_no() + "' ";
                        }
                        else
                        {

                            sql += " and a.seal_no >= '" + model.getSeal_str_no() + "' ";
                        }
                      
                    }

                    

                    //条件 シールNo.(end）

                    if (!StringUtil.isEmpty(model.getSeal_end_no()))
                    {
                        
                            sql += " and a.seal_no <= '" + model.getSeal_end_no() + "' "; // end
                       

                    }

                    //条件 販売業者登録番号

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no like '" + model.getTrader_reg_no1() + "%'";

                    }

                    if (StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no like '%" + model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sql += " and a.trader_reg_no = '" + model.getTrader_reg_no1() +"-"+ model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getOffice_name()))
                    {
                        sql += " and b.office_name like '%" + model.getOffice_name() + "%' and b.branch_code=0 ";
                    }

                    if (!StringUtil.isEmpty(model.getOffice_kana()))
                    {
                        sql += " and b.office_kana like '%" + model.getOffice_kana() + "%' and b.branch_code=0 ";
                    }

                    if (!StringUtil.isEmpty(model.getBranch_name()))
                    {
                        sql += " and b.office_name like '%" + model.getOffice_name() + "%' and b.branch_code>0 ";
                    }

                    sql += " and a.union_code=b.union_code and a.branch_code=b.branch_code ";


                    string nsql = "select top " + (pageNo * pageSize) + " * from trn_purch_seal as a,mst_union_view as b where " + sql;

                    ReportSearchModel report = null;

                    LogManager.info("TRN_Report_HPersister search query : " + nsql);

                    
                    SqlParameter[] sqlParameter = { };
                    SqlDataReader dataReader = db.GetDataCmd(nsql, sqlParameter);
                    int machine_total_qty = 0;
                    while (dataReader.Read())
                    {

                        report = new ReportSearchModel();

                        report.setReport_date((DateTime)dataReader["upd_date"]);

                        report.setSeal_str_no((string)dataReader["seal_no"]);
                        report.setReport_no((string)dataReader["seal_no"]);

                        report.setUnion_code((string)dataReader["union_code"]);

                        report.setBranch_code((short)dataReader["branch_code"]);

                        report.setTrader_reg_no(((string)dataReader["trader_reg_no"]).Replace("-", ""));


                        report.setReport_sts((string)dataReader["purch_sts"]);
                       
                        report.setMachine_qty(1);
                        machine_total_qty = machine_total_qty + 1;

                        list.Add(report);

                    }

                    dataReader.Close();

                    int count = 0;

                    String pssql = "select count(*) from trn_purch_seal as a,mst_union_view as b where " + sql;

                    LogManager.info("TRN_Report_HPersister search count query : " + pssql);


                    dataReader = db.GetDataCmd(pssql, sqlParameter);

                    if (dataReader.Read())
                    {
                        count = (int)dataReader[0];
                    }

                    if (pageNo == 0)
                    {
                        pageNo = 1;
                    }

                    Pager pager = new Pager();
                    pager.PageNo = pageNo;
                    pager.PageSize = pageSize;
                    pager.Total = count;
                    pager.Other = count.ToString();
                    pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
                    ArrayList result = new ArrayList();
                    object[] bsm = list.ToArray();
                    if (list.Count == 1)
                    {
                        pager.List = list;
                    }
                    else
                    {
                        //((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize))
                        for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
                        {
                            result.Add((ReportSearchModel)bsm[i]);

                        }
                        pager.List = result;

                    }
                    //pager.List = list;

                    db.Close();

                    return pager;
                }
                catch (Exception e2)
                {
                    db.Close();
                    LogManager.error("trn_report_h_persister " + e2.Message + e2.StackTrace);
                }

            }
            else
            {

                bool unionflag = false;

                if (!StringUtil.isEmpty(model.getOffice_name()) || !StringUtil.isEmpty(model.getOffice_kana()) || !StringUtil.isEmpty(model.getBranch_name()))
                {

                    unionflag = true;

                }

                bool detailflag = false;
                
                if (!StringUtil.isEmpty(model.getSeal_str_no()) || !StringUtil.isEmpty(model.getSeal_end_no()))
                {

                    detailflag = true;

                }

                bool traderflag = false;

                if (!StringUtil.isEmpty(model.getTrader_name()) || !StringUtil.isEmpty(model.getTrader_kana()))
                {

                    traderflag = true;

                }

                bool machineclassflag = false;

                if (!StringUtil.isEmpty(model.getMachine_clss()))
                {
                    machineclassflag = true;

                }
                SqlDB db = new SqlDB();
                try
                {
                    string sqlwhere = "";
                    String sql = "select distinct top "+(pageNo*pageSize) +" r.report_date as report_date_order," + "  r.report_date as report_date," + "  r.report_no as report_no," + "  r.union_code as union_code," + "  r.branch_code as branch_code," +

                "  r.hall_name as hall_name, " + //ホール名

                "  r.hall_pref_code as hall_pref_code, " + //ホール所在地・都道府県

                "  r.hall_city as hall_city, " + //ホール所在地・市区町村

                "  r.hall_address as hall_address, " + //ホール所在地・番地以降

                "  r.hall_tel as hall_tel, "; //ホール所在地・番地以降

                    if (machineclassflag)
                    {
                        sql = sql + "  d.machine_clss as machine_clss, ";
                    }

                    sql = sql + "  r.machine_name as machine_name,"
                        + "  r.trader_reg_no as trader_reg_no,"
                        + "  r.est_date as est_date,"
                        + "  r.machine_qty as machine_qty,"
                        + "  r.report_clss as report_clss,"
                        + "  r.report_sts as report_sts,"
                        + "  r.fax_error_flg as fax_error_flg,"
                        + "  r.dup_error_flg as dup_error_flg,"
                        + "  r.dif_report_flg as dif_report_flg ";

                    sqlwhere +=" from trn_report_h as r ";

                    if (unionflag)
                    {

                        sqlwhere += "  ,mst_union_view as u  ,mst_union_view as k ";

                    }

                    if (traderflag)
                    {

                        sqlwhere += "  ,mst_trader as t ";

                    }
                    /*
                    if (detailflag || machineclassflag || model.getCansel())
                    {

                        sql += "  ,trn_report_d as d ";

                    }
                     * */

                   if (detailflag || machineclassflag)
                    {

                        sqlwhere += "  ,trn_report_d as d ";

                    }

                    sqlwhere += " where " + " r.report_sts not in ( '" + TrnReporth.STATUS_DELETE + "','" + TrnReporth.STATUS_DENIAL + "' ) ";

                    if (unionflag)
                    {

                        sqlwhere += " and r.union_code = u.union_code " + " and r.union_code = k.union_code " + " and r.branch_code = u.branch_code " + " and k.branch_code = 0 ";

                    }

                    if (traderflag)
                    {

                        sqlwhere += " and r.trader_reg_no = t.trader_reg_no ";

                    }

                    if (detailflag)
                    {

                        sqlwhere += "  and r.report_no = d.report_no ";

                    }

                    //検索条件を生成

                    //条件 開始　Report_date_str

                    if (!StringUtil.isEmpty(model.getReport_date_str()))
                    {

                        sqlwhere += " and CONVERT(varchar(20),r.report_date,112) >= '" + model.getReport_date_str() + "' ";

                    }

                    //条件 終了　Report_date_end

                    if (!StringUtil.isEmpty(model.getReport_date_end()))
                    {

                        sqlwhere += " and CONVERT(varchar(20),r.report_date,112) <= '" + model.getReport_date_end() + "' ";

                    }

                    //条件 Union_code

                    if (!StringUtil.isEmpty(model.getUnion_code()))
                    {

                        sqlwhere += " and r.union_code = '" + model.getUnion_code() + "'";

                    }

                    //条件 Branch_code

                    if (model.getBranch_code() >= 0)
                    {

                        sqlwhere += " and r.branch_code = " + model.getBranch_code();

                    }


                    //条件 組合員名

                    if (!StringUtil.isEmpty(model.getOffice_name()))
                    {

                        sqlwhere += " and k.office_name like '%" + model.getOffice_name() + "%'";

                    }

                    //条件 組合員カナ

                    if (!StringUtil.isEmpty(model.getOffice_kana()))
                    {

                        sqlwhere += " and k.office_kana like '%" + model.getOffice_kana() + "%'";

                    }

                    //条件 支店名

                    if (!StringUtil.isEmpty(model.getBranch_name()))
                    {

                        sqlwhere += " and u.office_name like '%" + model.getBranch_name() + "%' ";

                    }

                    if (!StringUtil.isEmpty(model.getSeal_str_no()) && !StringUtil.isEmpty(model.getSeal_end_no()))
                    {

                        
                        sqlwhere += " and (( d.seal_str_no !='' and d.seal_end_no !='' and d.seal_end_no >= '" + model.getSeal_str_no() + "' and d.seal_str_no <= '" + model.getSeal_end_no() + "')"; // str, end

                        sqlwhere += " or ( d.seal_str_no !='' and d.seal_end_no ='' and d.seal_str_no >= '" + model.getSeal_str_no() + "' and d.seal_str_no <= '" + model.getSeal_end_no() + "'))"; // str, end
                        
                        /*sqlwhere += " and (d.seal_str_no >= '" + model.getSeal_str_no() + "' and d.seal_end_no <='" + model.getSeal_end_no() + "')";*/

                    }

                    //条件 シールNo.(str）

                    // 開始番号のみ入力された場合、単一番号の検索として扱われる。

                    // ここへ来るまでに終了番号へ開始番号が入力されるため、以下のルーチンには入らない。

                    if (!StringUtil.isEmpty(model.getSeal_str_no()) && StringUtil.isEmpty(model.getSeal_end_no()))
                    {

                        //sqlwhere += " and (( d.seal_str_no !='' and d.seal_end_no !='' and d.seal_str_no <= '" + model.getSeal_str_no() + "' and d.seal_end_no >= '" + model.getSeal_str_no() + "')"; // str, str

                        //sqlwhere += " or ( d.seal_str_no !='' and d.seal_end_no ='' and d.seal_str_no = '" + model.getSeal_str_no() + "'))"; // str
                       // sqlwhere += " and ( d.seal_str_no !='' and d.seal_end_no ='' and d.seal_str_no = '" + model.getSeal_str_no() + "')"; // str
                        sqlwhere += " and ((d.seal_str_no <= '" + model.getSeal_str_no() + "' and d.seal_end_no >='"+model.getSeal_str_no()+"')"; // str
                        sqlwhere += " or (d.seal_str_no = '" + model.getSeal_str_no() + "' and d.seal_end_no =''))"; // str

                    }

                    //条件 シールNo.(end）

                    if (StringUtil.isEmpty(model.getSeal_str_no()) && !StringUtil.isEmpty(model.getSeal_end_no()))
                    {

                       // sqlwhere += " and ((d.seal_end_no <= '" + model.getSeal_end_no() + "' and d.seal_end_no!='' ) or (d.seal_end_no='' and d.seal_str_no<='" + model.getSeal_end_no() + "'))"; // e
                        sqlwhere += " and  d.seal_str_no<='" + model.getSeal_end_no() + "'"; // 

                    }

                    //条件 ホール名

                    if (!StringUtil.isEmpty(model.getHall_name()))
                    {

                        //2004/07/22 T.okawa 1Line コメントアウト＋追加

                        //sql += " and h.hall_name like ? ";

                        sqlwhere += " and r.hall_name like '%" + model.getHall_name() + "%'";

                    }

                    //条件 設置ホール・都道府県

                    if (model.getHall_pref_code() != 0)
                    {

                        sqlwhere += " and r.hall_pref_code = " + model.getHall_pref_code();

                    }

                    //条件 設置ホール・市区町村

                    if (!StringUtil.isEmpty(model.getHall_city()))
                    {

                        sqlwhere += " and r.hall_city like '%" + model.getHall_city() + "%'";

                    }

                    //条件 販売業者登録番号

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlwhere += " and r.trader_reg_no like '" + model.getTrader_reg_no1() + "%'";

                    }

                    if (StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlwhere += " and r.trader_reg_no like '%" + model.getTrader_reg_no2() + "'";

                    }

                    if (!StringUtil.isEmpty(model.getTrader_reg_no1()) && !StringUtil.isEmpty(model.getTrader_reg_no2()))
                    {

                        sqlwhere += " and r.trader_reg_no = '" + model.getTrader_reg_no1() +"-"+ model.getTrader_reg_no2() + "'";

                    }

                    //条件 設置業者名

                    if (!StringUtil.isEmpty(model.getTrader_name()))
                    {

                        sqlwhere += " and t.trader_name like '%" + model.getTrader_name() + "%'";

                    }

                    //条件 設置業者名（カナ）

                    if (!StringUtil.isEmpty(model.getTrader_kana()))
                    {

                        sqlwhere += " and t.trader_kana like '%" + model.getTrader_kana() + "%'";

                    }

                    //条件 設置日開始　
                    if (!StringUtil.isEmpty(model.getEst_date_str()))
                    {

                        sqlwhere += " and CONVERT(varchar(20),r.est_date,112) >= '" + model.getEst_date_str() + "'";

                    }

                    //条件 設置日終了　
                    if (!StringUtil.isEmpty(model.getEst_date_end()))
                    {

                        sqlwhere += " and CONVERT(varchar(20), r.est_date,112 ) <= '" + model.getEst_date_end() + "'";

                    }

                    //条件 取扱主任者証番号

                    if (!StringUtil.isEmpty(model.getChief_no()))
                    {

                        sqlwhere += " and r.chief_no = '" + model.getChief_no() + "'";

                    }

                    //条件 主任者名

                    if (!StringUtil.isEmpty(model.getChief_name()))
                    {

                        sqlwhere += " and r.chief_name like '%" + model.getChief_name() + "%'";

                    }

                    //条件 状態

                    if (!StringUtil.isEmpty(model.getReport_sts()))
                    {

                        sqlwhere += " and r.report_sts = '" + model.getReport_sts() + "'";

                    }

                    //条件 報告区分

                    if (!StringUtil.isEmpty(model.getReport_clss()))
                    {

                        sqlwhere += " and r.report_clss = '" + model.getReport_clss() + "'";

                    }

                    //条件 エラー

                    if (!StringUtil.isEmpty(model.getReport_err()))
                    {

                        if ("1".Equals(model.getReport_err()))
                        {

                            sqlwhere += " and ( r.fax_error_flg = '1' or r.dup_error_flg = '1' or r.dif_report_flg = '1' ) ";

                        }

                    }

                    //条件 報告番号

                    if (!StringUtil.isEmpty(model.getReport_no()))
                    {

                        sqlwhere += " and r.report_no = '" + model.getReport_no() + "'";

                    }

                    if (machineclassflag)
                    {
                        sqlwhere += " and d.report_no = r.report_no and d.machine_clss = '" + model.getMachine_clss() + "'";
                    }

                    if (model.getCansel())
                    {
                        // sql += " and ( d.seal_str_no in (select seal_no from trn_purch_seal t1 where r.union_code=t1.union_code and r.branch_code=t1.branch_code and  purch_sts='" + TrnPurchSeal_a.STATUS_CANSEL + "')  and d.seal_end_no in (select seal_no from trn_purch_seal t2 where r.union_code=t2.union_code and r.branch_code=t2.branch_code and  purch_sts='" + TrnPurchSeal_a.STATUS_CANSEL + "') )";
                    }


                    string newsql = "";
                    
                    if ((!StringUtil.isEmpty(model.getSeal_str_no()) || !StringUtil.isEmpty(model.getSeal_end_no())) && StringUtil.isEmpty(model.getReport_sts()))
                    {
                        newsql = sql + sqlwhere + " union all " + sqlFrom + sqlFromwhere;
                    }
                    else
                    {
                        newsql = sql + sqlwhere;
                    }
                    

                    //newsql = sql + sqlwhere;

                    newsql += " order by report_date_order desc,r.report_no desc ";


                    ReportSearchModel report = null;

                    int machine_total_qty = 0;

                    LogManager.info("TRN_Report_HPersister search query : " + newsql);

                   
                    SqlParameter[] sqlParameter = { };
                    SqlDataReader dataReader = db.GetDataCmd(newsql, sqlParameter);

                    while (dataReader.Read())
                    {

                        report = new ReportSearchModel();

                        report.setReport_date((DateTime)dataReader["report_date"]);

                        report.setReport_no((string)dataReader["report_no"]);

                        report.setUnion_code((string)dataReader["union_code"]);

                        report.setBranch_code((short)dataReader["branch_code"]);

                        //下記if分全部T.okawa

                        report.setHall_name((string)dataReader["hall_name"]);

                        if (report.getHall_name() == null)
                        {

                            //ホール名がnullだったらブランクを入れる

                            report.setHall_name("");

                        }
                        //LogManager.info("TRN_Report_HPersister search query : " + dataReader["hall_pref_code"].ToString());
                        report.setHall_pref_code(short.Parse(dataReader["hall_pref_code"].ToString()));

                        if ((model.getHall_pref_code().ToString()) == null)
                        {

                            //ホール所在地・都道府県コードがnullだったら0を入れる

                            report.setHall_pref_code(0);

                        }

                        report.setHall_city((string)dataReader["hall_city"]);

                        if (report.getHall_city() == null)
                        {

                            //ホール所在地・市区町村がnullだったらブランクを入れる

                            report.setHall_city("");

                        }

                        report.setHall_address((string)dataReader["hall_address"]);

                        if (report.getHall_address() == null)
                        {

                            //ホール所在地・市区町村がnullだったらブランクを入れる

                            report.setHall_address("");

                        }

                        report.setHall_tel((string)dataReader["hall_tel"]);

                        if (report.getHall_tel() == null)
                        {

                            //ホール所在地・電話番号がnullだったらブランクを入れる

                            report.setHall_tel("");

                        }

                        report.setMachine_name((string)dataReader["machine_name"]);

                        //report.setTrader_reg_no((string)dataReader["trader_reg_no"]);
                        report.setTrader_reg_no(((string)dataReader["trader_reg_no"]).Replace("-", ""));
                        

                        report.setEst_date((DateTime)dataReader["est_date"]);

                        report.setMachine_qty((int)dataReader["machine_qty"]);

                        report.setReport_clss((string)dataReader["report_clss"]);

                        report.setReport_sts((string)dataReader["report_sts"]);

                        if ((string)dataReader["fax_error_flg"] == null)
                        {
                            report.setFax_error_flg("0");
                        }
                        else
                        {
                            report.setFax_error_flg((string)dataReader["fax_error_flg"]);

                        }

                        if ((string)dataReader["dup_error_flg"] == null)
                        {
                            report.setDup_error_flg("0");
                        }
                        else
                        {
                            report.setDup_error_flg((string)dataReader["dup_error_flg"]);
                        }

                        if ((string)dataReader["dif_report_flg"] == null)
                        {
                            report.setDif_report_flg("0");
                        }
                        else
                        {
                            report.setDif_report_flg((string)dataReader["dif_report_flg"]);
                        }

                       // machine_total_qty = machine_total_qty + report.getMachine_qty();

                        list.Add(report);

                    }

                    dataReader.Close();

                    //=============================================

                    int count = 0;

                    String pssql = "select count(distinct r.report_no) " + sqlwhere;

                    LogManager.info("TRN_Report_HPersister search count query  1: " + pssql);


                    dataReader = db.GetDataCmd(pssql, sqlParameter);

                    if (dataReader.Read())
                    {
                        count = (int)dataReader[0];
                    }

                    dataReader.Close();

                    //======================================================

                    if ((!StringUtil.isEmpty(model.getSeal_str_no()) || !StringUtil.isEmpty(model.getSeal_end_no())) && StringUtil.isEmpty(model.getReport_sts() ))
                    {
                        pssql = "select count(*) " + sqlFromwhere;

                        LogManager.info("TRN_Report_HPersister search count query  11: " + pssql);
                        dataReader = db.GetDataCmd(pssql, sqlParameter);

                        if (dataReader.Read())
                        {
                            count = count + (int)dataReader[0];
                        }

                        dataReader.Close();
                        LogManager.info("TRN_Report_HPersister search count query  11: " + count);
                    }
                    
                    //=========================================================

                    String pssql2 = "select sum(machine_qty)  from  (select distinct r.report_no,r.machine_qty" + sqlwhere + ") r";

                    dataReader = db.GetDataCmd(pssql2, sqlParameter);

                    if (dataReader.Read())
                    {
                        LogManager.info("TRN_Report_HPersister search count query 2: " + pssql2 + "  " + dataReader[0].ToString());
                        machine_total_qty = DBNull.Value == dataReader[0] ? 0 : (int)dataReader[0];

                    }

                    dataReader.Close();

                    db.Close();


                    if ((!StringUtil.isEmpty(model.getSeal_str_no()) || !StringUtil.isEmpty(model.getSeal_end_no())) && StringUtil.isEmpty(model.getReport_sts()))
                    {
                        pssql2 = "select count(*)  " + sqlFromwhere;
                        LogManager.info("TRN_Report_HPersister search count query 22: " + pssql2 );
                        dataReader = db.GetDataCmd(pssql2, sqlParameter);

                        if (dataReader.Read())
                        {
                            LogManager.info("TRN_Report_HPersister search count query 2: " + pssql2 + "  " + dataReader[0].ToString());
                            machine_total_qty += DBNull.Value == dataReader[0] ? 0 : (int)dataReader[0];

                        }

                        dataReader.Close();

                        db.Close();

                    }
                   
                   

                    //==============================================================


                    if (pageNo == 0)
                    {
                        pageNo = 1;
                    }

                    Pager pager = new Pager();
                    pager.PageNo = pageNo;
                    pager.PageSize = pageSize;
                    pager.Total = count;
                    pager.Other = machine_total_qty.ToString();
                    pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
                    ArrayList result = new ArrayList();
                    object[] bsm = list.ToArray();
                    if (list.Count == 1)
                    {
                        pager.List = list;
                    }
                    else
                    {
                        //((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize))
                        for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
                        {
                            result.Add((ReportSearchModel)bsm[i]);

                        }
                        pager.List = result;

                    }

                    return pager;

                }
                catch (Exception e)
                {
                    db.Close();
                    LogManager.error("TRN_Report_H_Persister search " + e.Message + e.StackTrace);
                }

            }


            return null;

          

	    }
        
        /**
	     * 報告No.を取得する。
	     * @param con コネクション
	     * @return String 生成されたレポートナンバー
	    */
        public string makeReportNo( )
        {
            LogManager.info("TRN_Report_H_Persister makeReportNo in ");
		    string autoNum=null;
            string dateString = null;
            string report_no = null;
            int dateint = 0;
            try {
                //YYを取得

			    dateString = System.DateTime.Now.ToString("yy");

			    LogManager.info("TRN_Report_H_Persister makeReportNo - dateString "+dateString);

			    try {

				    dateint = int.Parse(dateString);

			    } catch (Exception exxx) {

				    dateint = 1;

			    }

                int number = getSequenceNextVal(dateString, "REPORT_NO_SEQ");
                autoNum = string.Format("{0:0000000}", number);
                report_no = "S" + dateString + autoNum;
                
                LogManager.info("TRN_Report_H_Persister makeReportNo - autoNum "+autoNum);
        
			    LogManager.info("TRN_Report_H_Persister makeReportNo - report_no "+report_no);

		    } catch (Exception pe) {
                LogManager.error("TRN_Report_H_Persister makeReportNo "+pe.Message+pe.StackTrace);
			}

            return report_no;

	    }

        
        /**
	     * TRN_Report_Hテーブルにレコードを挿入します。
	     * @param	con	DBコネクション
	     * @param	model 報告見出モデル
	     * @return	boolean
	     */
	    public bool insert( SqlDB db ,TrnReporth model) {

		    string sql = "";

		    sql += "insert into TRN_Report_H " + "(report_no," + " report_clss," + " report_date," + " union_code," + " branch_code," + " report_tanto_name," + " est_comp_clss," + " cons_comp_name," + " cons_comp_telno,";

		    //if (model.HallSeqno > 0) {
			    sql += " hall_seqno,";
		    //}

		    sql += " machine_name,"
			    + " machine_qty,"
			    + " est_date,"
			    + " trader_reg_no,"
			    + " chief_no,"
			    + " chief_name,"
			    + " worker1_name,"
			    + " worker2_name,"
			    + " worker3_name,"
			    + " worker4_name,"
			    + " worker5_name,"
			    + " worker6_name,"
			    + " fax_rcv_date,"
			    + " ocr_pass_txt,"
			    + " fax_error_flg,"
			    + " dup_error_flg,"
			    + " dif_report_flg,"
			    + " faxocr_text1,"
			    + " faxocr_text2,"
			    + " faxocr_text3,"
			    + " faxocr_text4,"
			    + " faxocr_text5,"
			    + " faxocr_text6,"
			    + " faxocr_text7,"
			    + " faxocr_text8,"
			    + " faxocr_text9,"
			    + " faxocr_text10,"
			    + " faxocr_text11,"
			    + " faxocr_text12,"
			    + " faxocr_text13,"
			    + " report_sts,"
			    + " reg_date,"
			    + " upd_date,"
			    + " upd_union_code,"
			    + " upd_branch_code,"
			    + " upd_func_id,"
			    + " hall_name,"
			    + " hall_pref_code,"
			    + " hall_city,"
			    + " hall_address,"
			    + " hall_tel)	"
			    + "values ( "
                + " @report_no ,"
                + " @report_clss ,"
                + " @report_date ,"
                + " @union_code ,"
                + " @branch_code ,"
                + " @report_tanto_name ,"
                + " @est_comp_clss ,"
                + " @cons_comp_name ,"
                + " @cons_comp_telno ,";

		    //if (model.HallSeqno > 0) {
                sql += "@hall_seqno ,";
		   // }
            sql += " @machine_name ,"
                + " @machine_qty ,"
                + " @est_date ,"
                + " @trader_reg_no ,"
                + " @chief_no ,"
                + " @chief_name ,"
                + " @worker1_name ,"
                + " @worker2_name ,"
                + " @worker3_name ,"
                + " @worker4_name ,"
                + " @worker5_name ,"
                + " @worker6_name ,"
                + " @fax_rcv_date ,"
                + " @ocr_pass_txt ,"
                + " @fax_error_flg ,"
                + " @dup_error_flg ,"
                + " @dif_report_flg ,"
                + " @faxocr_text1 ,"
                + " @faxocr_text2 ,"
                + " @faxocr_text3 ,"
                + " @faxocr_text4 ,"
                + " @faxocr_text5 ,"
                + " @faxocr_text6 ,"
                + " @faxocr_text7 ,"
                + " @faxocr_text8 ,"
                + " @faxocr_text9 ,"
                + " @faxocr_text10 ,"
                + " @faxocr_text11 ,"
                + " @faxocr_text12 ,"
                + " @faxocr_text13 ,"
                + " @report_sts ,"
                + " @reg_date ,"
                + " @upd_date ,"
                + " @upd_union_code ,"
                + " @upd_branch_code ,"
                + " @upd_func_id ,"
                + " @hall_name ,"
                + " @hall_pref_code ,"
                + " @hall_city ,"
                + " @hall_address ,"
                + " @hall_tel )";

		    try {

                SqlParameter[] sqlParameter = {   
new SqlParameter("@report_no",SqlDbType.VarChar,20),         
new SqlParameter("@report_clss",SqlDbType.VarChar),       
new SqlParameter("@report_date" ,SqlDbType.DateTime),       
new SqlParameter("@union_code" ,SqlDbType.VarChar),        
new SqlParameter("@branch_code" ,SqlDbType.SmallInt),       
new SqlParameter("@report_tanto_name" ,SqlDbType.VarChar), 
new SqlParameter("@est_comp_clss",SqlDbType.VarChar),     
new SqlParameter("@cons_comp_name",SqlDbType.VarChar),    
new SqlParameter("@cons_comp_telno",SqlDbType.VarChar),  
new SqlParameter("@hall_seqno",SqlDbType.Int),       
new SqlParameter("@machine_name",SqlDbType.VarChar),      
new SqlParameter("@machine_qty",SqlDbType.Int),       
new SqlParameter("@est_date",SqlDbType.DateTime),          
new SqlParameter("@trader_reg_no",SqlDbType.VarChar),     
new SqlParameter("@chief_no",SqlDbType.VarChar),          
new SqlParameter("@chief_name",SqlDbType.VarChar),        
new SqlParameter("@worker1_name",SqlDbType.VarChar),      
new SqlParameter("@worker2_name",SqlDbType.VarChar),      
new SqlParameter("@worker3_name",SqlDbType.VarChar),      
new SqlParameter("@worker4_name",SqlDbType.VarChar),      
new SqlParameter("@worker5_name",SqlDbType.VarChar),      
new SqlParameter("@worker6_name",SqlDbType.VarChar),      
new SqlParameter("@fax_rcv_date",SqlDbType.DateTime),      
new SqlParameter("@ocr_pass_txt",SqlDbType.VarChar),     
new SqlParameter("@fax_error_flg",SqlDbType.VarChar), 
new SqlParameter("@dup_error_flg",SqlDbType.VarChar),     
new SqlParameter("@dif_report_flg",SqlDbType.VarChar),    
new SqlParameter("@faxocr_text1",SqlDbType.VarChar),
new SqlParameter("@faxocr_text2",SqlDbType.VarChar),
new SqlParameter("@faxocr_text3",SqlDbType.VarChar),
new SqlParameter("@faxocr_text4",SqlDbType.VarChar),
new SqlParameter("@faxocr_text5",SqlDbType.VarChar),
new SqlParameter("@faxocr_text6",SqlDbType.VarChar),
new SqlParameter("@faxocr_text7",SqlDbType.VarChar),
new SqlParameter("@faxocr_text8",SqlDbType.VarChar),
new SqlParameter("@faxocr_text9",SqlDbType.VarChar),
new SqlParameter("@faxocr_text10",SqlDbType.VarChar),
new SqlParameter("@faxocr_text11",SqlDbType.VarChar),
new SqlParameter("@faxocr_text12",SqlDbType.VarChar),
new SqlParameter("@faxocr_text13",SqlDbType.VarChar),
new SqlParameter("@report_sts",SqlDbType.VarChar),        
new SqlParameter("@reg_date",SqlDbType.DateTime),          
new SqlParameter("@upd_date",SqlDbType.DateTime),          
new SqlParameter("@upd_union_code",SqlDbType.VarChar),    
new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),   
new SqlParameter("@upd_func_id",SqlDbType.VarChar),       
new SqlParameter("@hall_name",SqlDbType.VarChar),         
new SqlParameter("@hall_pref_code",SqlDbType.SmallInt),    
new SqlParameter("@hall_city",SqlDbType.VarChar),         
new SqlParameter("@hall_address",SqlDbType.VarChar),      
new SqlParameter("@hall_tel",SqlDbType.VarChar )     
                                                };

sqlParameter[0].Value=model.ReportNo;
sqlParameter[1].Value=model.ReportClss;
sqlParameter[2].Value=model.ReportDate;
sqlParameter[3].Value=model.UnionCode;
sqlParameter[4].Value=model.BranchCode;
sqlParameter[5].Value=model.ReportTantoName;
sqlParameter[6].Value=model.EstCompClss;
sqlParameter[7].Value=model.ConsCompName;
sqlParameter[8].Value=model.ConsCompTelno;
sqlParameter[9].Value=model.HallSeqno;
sqlParameter[10].Value=model.MachineName;
sqlParameter[11].Value=model.MachineQty;
sqlParameter[12].Value=model.EstDate;
if (StringUtil.isEmpty(model.TraderRegNo))
{
    sqlParameter[13].Value = "";
}
else
{
    sqlParameter[13].Value = model.TraderRegNo.Substring(0, 2) + "-" + model.TraderRegNo.Substring(2, 5);
}
if (StringUtil.isEmpty(model.ChiefNo))
{
    sqlParameter[14].Value = "";
}
else
{
    sqlParameter[14].Value = model.ChiefNo.Substring(0, 2) + "-" + model.ChiefNo.Substring(2, 4);
}
sqlParameter[15].Value=model.ChiefName;
sqlParameter[16].Value=model.Worker1Name;
sqlParameter[17].Value=model.Worker2Name;
sqlParameter[18].Value=model.Worker3Name;
sqlParameter[19].Value=model.Worker4Name;
sqlParameter[20].Value=model.Worker5Name;
sqlParameter[21].Value=model.Worker6Name;

if (model.FaxRcvDate == null)
{
    sqlParameter[22].Value = DBNull.Value;
}
else
{
    sqlParameter[22].Value = model.FaxRcvDate;
}

sqlParameter[23].Value = model.OcrPassTxt == null ? "" : model.OcrPassTxt;
sqlParameter[24].Value = model.FaxErrorFlg == null ? "" : model.FaxErrorFlg;
sqlParameter[25].Value = model.DupErrorFlg == null ? "" : model.DupErrorFlg;
sqlParameter[26].Value = model.DifReportFlg == null ? "" : model.DifReportFlg;
sqlParameter[27].Value = model.FaxocrText1 == null ? "" : model.FaxocrText1;
sqlParameter[28].Value = model.FaxocrText2 == null ? "" : model.FaxocrText2;
sqlParameter[29].Value = model.FaxocrText3 == null ? "" : model.FaxocrText3;
sqlParameter[30].Value = model.FaxocrText4 == null ? "" : model.FaxocrText4;
sqlParameter[31].Value = model.FaxocrText5 == null ? "" : model.FaxocrText5;
sqlParameter[32].Value = model.FaxocrText6 == null ? "" : model.FaxocrText6;
sqlParameter[33].Value = model.FaxocrText7 == null ? "" : model.FaxocrText7;
sqlParameter[34].Value = model.FaxocrText8 == null ? "" : model.FaxocrText8;
sqlParameter[35].Value = model.FaxocrText9 == null ? "" : model.FaxocrText9;
sqlParameter[36].Value = model.FaxocrText10 == null ? "" : model.FaxocrText10;
sqlParameter[37].Value = model.FaxocrText11 == null ? "" : model.FaxocrText11;
sqlParameter[38].Value = model.FaxocrText12 == null ? "" : model.FaxocrText12;
sqlParameter[39].Value = model.FaxocrText13 == null ? "" : model.FaxocrText13;
sqlParameter[40].Value=model.ReportSts;
sqlParameter[41].Value=model.RegDate;
sqlParameter[42].Value=model.UpdDate;
sqlParameter[43].Value=model.UpdUnionCode;
sqlParameter[44].Value=model.UpdBranchCode;
sqlParameter[45].Value=model.UpdFuncId;
sqlParameter[46].Value=model.HallName;
sqlParameter[47].Value=model.HallPrefCode;
sqlParameter[48].Value=model.HallCity;
sqlParameter[49].Value=model.HallAddress;
sqlParameter[50].Value=model.HallTel;

db.ExecuteCmd(sql,sqlParameter);

		    } catch (Exception exe) {

                LogManager.error("TRN_Report_H_Persister insert "+exe.Message+exe.StackTrace);
                throw new Exception("TRN_Report_H_Persister insert error");
                //return false;

		    } 

		    return true;

	    }

        public bool insertHall(SqlDB db, TrnReporth model)
        {

            LogManager.info("TRN_Report_H_Persister insertHall start.......");

            //select max(hall_seqno)+1 from mst_hall;
            string sql1 = "select max(hall_seqno)+1 from mst_hall";
            int id = 0;
            SqlParameter[] sqlParameter1 = {  };
            SqlDataReader dataReader1 = db.GetDataCmd(sql1, sqlParameter1);
            if (dataReader1.Read())
            {
                id = (int)dataReader1[0];
            }
            dataReader1.Close();

            string sql2 = "select count(*) from mst_hall where hall_name=@hall_name  and hall_pref_code=@hall_pref_code  and  (hall_city+hall_address)=@hall_city_hall_address and hall_tel=@hall_tel  ";
            int count = 0;

            SqlParameter[] sqlParameter2 = {   
new SqlParameter("@hall_name",SqlDbType.VarChar,20),         
new SqlParameter("@hall_pref_code",SqlDbType.SmallInt),       
new SqlParameter("@hall_city_hall_address" ,SqlDbType.VarChar),        
new SqlParameter("@hall_tel" ,SqlDbType.VarChar)};
            sqlParameter2[0].Value = model.HallName;
            sqlParameter2[1].Value = model.HallPrefCode;
            sqlParameter2[2].Value = model.HallCity+model.HallAddress;
            sqlParameter2[3].Value = model.HallTel;
            

            SqlDataReader dataReader2 = db.GetDataCmd(sql2, sqlParameter2);
            if (dataReader2.Read())
            {
                count = DBNull.Value==dataReader2[0]?0:(int)dataReader2[0];
            }
            dataReader2.Close();

            if (count == 0)
            {
                string sql = "";

                sql += "insert into mst_hall " + "(";

                sql += " reg_date,"
                    + " upd_date,"
                    + " upd_union_code,"
                    + " upd_branch_code,"
                    + " upd_func_id,"
                    + " hall_name,"
                    + " hall_pref_code,"
                    + " hall_city,"
                    + " hall_address,"
                    + " hall_tel,"
                    + " comp_name,"
                    + " daihyo_name,"
                    + " hall_sts,"
                    + " union_code,"
                    + " branch_code,"
                    + " hall_code1,"
                    + " hall_code2,"
                    + " hall_seqno)"
                    + "values ( "
                    + " @reg_date ,"
                    + " @upd_date ,"
                    + " @upd_union_code ,"
                    + " @upd_branch_code ,"
                    + " @upd_func_id ,"
                    + " @hall_name ,"
                    + " @hall_pref_code ,"
                    + " @hall_city ,"
                    + " @hall_address ,"
                    + " @hall_tel ,"
                    + " @comp_name ,"
                    + " @daihyo_name ,"
                    + " @hall_sts,"
                    + " @union_code,"
                    + " @branch_code,"
                    + " @hall_code1,"
                    + " @hall_code2,"
                    + " @hall_seqno)";

                try
                {

                    SqlParameter[] sqlParameter = {   
new SqlParameter("@reg_date",SqlDbType.DateTime),         
new SqlParameter("@upd_date",SqlDbType.DateTime),       
new SqlParameter("@upd_union_code" ,SqlDbType.VarChar),       
new SqlParameter("@upd_branch_code" ,SqlDbType.SmallInt),        
new SqlParameter("@upd_func_id" ,SqlDbType.VarChar),       
new SqlParameter("@hall_name" ,SqlDbType.VarChar), 
new SqlParameter("@hall_pref_code",SqlDbType.VarChar),     
new SqlParameter("@hall_city",SqlDbType.VarChar),    
new SqlParameter("@hall_address",SqlDbType.VarChar),  
new SqlParameter("@hall_tel",SqlDbType.VarChar),       
new SqlParameter("@comp_name",SqlDbType.VarChar),      
new SqlParameter("@daihyo_name",SqlDbType.VarChar),
new SqlParameter("@hall_sts",SqlDbType.VarChar),  
new SqlParameter("@union_code",SqlDbType.VarChar),
new SqlParameter("@branch_code",SqlDbType.SmallInt),
new SqlParameter("@hall_code1",SqlDbType.Int),
new SqlParameter("@hall_code2",SqlDbType.SmallInt),
new SqlParameter("@hall_seqno",SqlDbType.Int)
                                              };

                    sqlParameter[0].Value = model.RegDate;
                    sqlParameter[1].Value = model.UpdDate;
                    sqlParameter[2].Value = model.UpdUnionCode;
                    sqlParameter[3].Value = model.UpdBranchCode;
                    sqlParameter[4].Value = "insert hall";
                    sqlParameter[5].Value = model.HallName;
                    sqlParameter[6].Value = model.HallPrefCode;
                    sqlParameter[7].Value = model.HallCity;
                    sqlParameter[8].Value = model.HallAddress;
                    sqlParameter[9].Value = model.HallTel;
                    sqlParameter[10].Value = model.ConsCompName;
                    sqlParameter[11].Value = "";
                    sqlParameter[12].Value = "10";
                    sqlParameter[13].Value = model.UnionCode;
                    sqlParameter[14].Value = model.BranchCode;
                    sqlParameter[15].Value = id;
                    sqlParameter[16].Value = 1;
                    sqlParameter[17].Value = id;

                    db.ExecuteCmd(sql, sqlParameter);

                }
                catch (Exception exe)
                {

                    LogManager.error("TRN_Report_H_Persister insert hall " + exe.Message + exe.StackTrace);
                    throw new Exception("TRN_Report_H_Persister insert hall error");
                    //return false;

                }
            }

            return true;

        }
        
        /**
	     * TRN_Report_Hテーブルからレコードを取得します。
	     * @param	con				DBコネクション
	     * @param	report_no 		報告No.
	     * @return	TRN_Report_H	報告見出モデル
	    */

	    public TrnReporth getDetail(string report_no) {
            
		    LogManager.info("TRN_Report_H_Persister getDetail() --- start! ");

		    LogManager.info("TRN_Report_H_Persister parameter report_no : " + report_no);

            TrnReporth model = null;

		    string sql = "select " + " report_no," + " report_clss," + " report_date," + " union_code," + " branch_code," + " report_tanto_name," + " est_comp_clss," + " cons_comp_name," + " cons_comp_telno," +

	    " hall_name,"
		    + " hall_pref_code,"
		    + " hall_city,"
		    + " hall_address,"
		    + " hall_tel,"
		    + " machine_name,"
		    + " machine_qty,"
		    + " est_date,"
		    + " trader_reg_no,"
		    + " chief_no,"
		    + " chief_name,"
		    + " worker1_name,"
		    + " worker2_name,"
		    + " worker3_name,"
		    + " worker4_name,"
		    + " worker5_name,"
		    + " worker6_name,"
		    + " fax_rcv_date,"
		    + " ocr_pass_txt,"
		    + " fax_error_flg,"
		    + " dup_error_flg,"
		    + " dif_report_flg,"
		    + " faxocr_text1,"
		    + " faxocr_text2,"
		    + " faxocr_text3,"
		    + " faxocr_text4,"
		    + " faxocr_text5,"
		    + " faxocr_text6,"
		    + " faxocr_text7,"
		    + " faxocr_text8,"
		    + " faxocr_text9,"
		    + " faxocr_text10,"
		    + " faxocr_text11,"
		    + " faxocr_text12,"
		    + " faxocr_text13,"
		    + " report_sts,"
		    + " reg_date,"
		    + " upd_date,"
		    + " upd_union_code,"
		    + " upd_branch_code,"
		    + " upd_func_id	"
		    + " from TRN_Report_H"
            + " where report_no = @report_no "
		    + " order by report_no";

            SqlDB db = new SqlDB();

            try
            {

                SqlParameter[] sqlParameter = { new SqlParameter("@report_no", SqlDbType.VarChar, 20) };
                sqlParameter[0].Value = report_no;
                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);
                while (dataReader.Read())
                {

                    model = new TrnReporth();
                    model.ReportNo = (string)dataReader["report_no"];
                    model.ReportClss = (string)dataReader["report_clss"];
                    model.ReportDate = (DateTime)dataReader["report_date"];
                    model.UnionCode = (string)dataReader["union_code"];
                    model.BranchCode = (short)dataReader["branch_code"];
                    model.ReportTantoName = (string)dataReader["report_tanto_name"];
                    model.EstCompClss = (string)dataReader["est_comp_clss"];
                    model.ConsCompName = (string)dataReader["cons_comp_name"];
                    model.ConsCompTelno = (string)dataReader["cons_comp_telno"];
                    //下記if分全部T.okawa
                    model.HallName = (string)dataReader["hall_name"];
                    if (model.HallName == null)
                    {
                        model.HallName = "";
                    }
                    model.HallPrefCode = (short)dataReader["hall_pref_code"];
                    model.HallCity = (string)dataReader["hall_city"];

                    if (model.HallCity == null)
                    {
                        //ホール所在地・市区町村がnullだったらブランクを入れる
                        model.HallCity = "";
                    }
                    model.HallAddress = (string)dataReader["hall_address"];

                    if (model.HallAddress == null)
                    {
                        //ホール所在地・市区町村がnullだったらブランクを入れる
                        model.HallAddress = "";

                    }

                    model.HallTel = (string)dataReader["hall_tel"];

                    if (model.HallTel == null)
                    {

                        //ホール所在地・電話番号がnullだったらブランクを入れる
                        model.HallTel = "";

                    }

                    model.MachineName = (string)dataReader["machine_name"];

                    model.MachineQty = (int)dataReader["machine_qty"];

                    model.EstDate = (DateTime)dataReader["est_date"];

                    model.TraderRegNo = (string)dataReader["trader_reg_no"];
                    model.TraderRegNo = model.TraderRegNo.Replace("-", "");

                    model.ChiefNo = (string)dataReader["chief_no"];
                    model.ChiefNo = model.ChiefNo.Replace("-", ""); ;

                    model.ChiefName = (string)dataReader["chief_name"];

                    model.Worker1Name = (string)dataReader["worker1_name"];
                    model.Worker2Name = (string)dataReader["worker2_name"];
                    model.Worker3Name = (string)dataReader["worker3_name"];
                    model.Worker4Name = (string)dataReader["worker4_name"];
                    model.Worker5Name = (string)dataReader["worker5_name"];
                    model.Worker6Name = (string)dataReader["worker6_name"];

                    model.FaxRcvDate = (DBNull.Value == dataReader["fax_rcv_date"]) ? null : (DateTime?)dataReader["fax_rcv_date"];
                    model.OcrPassTxt = (string)dataReader["ocr_pass_txt"];
                    model.FaxErrorFlg = (string)dataReader["fax_error_flg"];
                    model.DupErrorFlg = (string)dataReader["dup_error_flg"];
                    model.DifReportFlg = (string)dataReader["dif_report_flg"];
                    model.FaxocrText1 = (string)dataReader["faxocr_text1"];
                    model.FaxocrText2 = (string)dataReader["faxocr_text2"];
                    model.FaxocrText3 = (string)dataReader["faxocr_text3"];
                    model.FaxocrText4 = (string)dataReader["faxocr_text4"];
                    model.FaxocrText5 = (string)dataReader["faxocr_text5"];
                    model.FaxocrText6 = (string)dataReader["faxocr_text6"];
                    model.FaxocrText7 = (string)dataReader["faxocr_text7"];
                    model.FaxocrText8 = (string)dataReader["faxocr_text8"];
                    model.FaxocrText9 = (string)dataReader["faxocr_text9"];
                    model.FaxocrText10 = (string)dataReader["faxocr_text10"];
                    model.FaxocrText11 = (string)dataReader["faxocr_text11"];
                    model.FaxocrText12 = (string)dataReader["faxocr_text12"];
                    model.FaxocrText13 = (string)dataReader["faxocr_text13"];
                    model.ReportSts = (string)dataReader["report_sts"];
                    model.RegDate = (DateTime)dataReader["reg_date"];
                    model.UpdDate = (DateTime)dataReader["upd_date"];
                    model.UpdUnionCode = (string)dataReader["upd_union_code"];
                    model.UpdBranchCode = (short)dataReader["upd_branch_code"];
                    model.UpdFuncId = (string)dataReader["upd_func_id"];
                }

            }
            catch (Exception ex)
            {
                LogManager.error("TRN_Report_H_Persister " + ex.Message + ex.StackTrace);
            }
            finally
            {
                db.Close();
            }

		    return model;

	    }

        /**
	     * TRN_Report_Dテーブルからレコード論理削除します。
	     * @param	con			DBコネクション
	     * @param	model	 	報告見出モデル
	     * @return	int
	     * @throws	PersisterSQLException
	    */

	    public void delete( SqlDB db ,TrnReporth model) {

		    int ret = -1;

		    string sql = "update TRN_Report_H set report_sts = @report_sts where report_no = @report_no";

		    try {

			    SqlParameter[] sqlParametertemp = {
                                                      new SqlParameter("@report_sts",SqlDbType.VarChar), 
                                                      new SqlParameter("@report_no",SqlDbType.VarChar)
                                                  };
                sqlParametertemp[0].Value=model.ReportSts;
                sqlParametertemp[1].Value=model.ReportNo;


			   // SqlDB db=new SqlDB();
                db.ExecuteCmd(sql,sqlParametertemp);
               // db.Close();
			    LogManager.info("TRN_Report_H_Persister delete Report  : " + model.ReportNo);

		    } catch (Exception e) {
                LogManager.error("TRN_Report_H_Persister : " + e.Message+e.StackTrace);
			  
		    } 


	    }


        /**
         * TRN_Report_Hテーブルからレコード更新します。
         * @param	con				DBコネクション
         * @param	model	 		報告見出モデル
         * @return	boolean
         * @throws	PersisterSQLException
        */
        public bool update(SqlDB db, TrnReporth model) {

		    string sql = "update TRN_Report_H set" + " report_date = @report_date," + " union_code = @union_code," + " branch_code = @branch_code," + " report_tanto_name = @report_tanto_name," + " est_comp_clss = @est_comp_clss," + " cons_comp_name = @cons_comp_name," + " cons_comp_telno = @cons_comp_telno," +

	    " machine_name = @machine_name,"
		    + " machine_qty = @machine_qty,"
		    + " est_date = @est_date,"
		    + " trader_reg_no = @trader_reg_no,"
		    + " chief_no = @chief_no,"
		    + " chief_name = @chief_name,"
		    + " worker1_name = @worker1_name,"
		    + " worker2_name = @worker2_name,"
		    + " worker3_name = @worker3_name,"
		    + " worker4_name = @worker4_name,"
		    + " worker5_name = @worker5_name,"
		    + " worker6_name = @worker6_name,"
		    + " fax_error_flg = @fax_error_flg,"
		    + " dup_error_flg = @dup_error_flg,"
		    + " dif_report_flg = @dif_report_flg,"
		    + " faxocr_text1 = '',"
		    + " faxocr_text2 = '',"
		    + " faxocr_text3 = '',"
		    + " faxocr_text4 = '',"
		    + " faxocr_text5 = '',"
		    + " faxocr_text6 = '',"
		    + " faxocr_text7 = '',"
		    + " faxocr_text8 = '',"
		    + " faxocr_text9 = '',"
		    + " faxocr_text10 = '',"
		    + " faxocr_text11 = '',"
		    + " faxocr_text12 = '',"
		    + " faxocr_text13 = '',"
		    + " upd_date = @upd_date,"
		    + " upd_union_code = @upd_union_code,"
		    + " upd_branch_code = @upd_branch_code,"
		    + " upd_func_id = @upd_func_id,"
		    + 
	    " hall_name = @hall_name," + " hall_pref_code = @hall_pref_code," + " hall_city = @hall_city," + " hall_address = @hall_address," + " hall_tel = @hall_tel" + " where " + " report_no = @report_no ";

		    try {

			    SqlParameter[] sqlParameter = {   
new SqlParameter("@report_no",SqlDbType.VarChar,20),         
new SqlParameter("@report_clss",SqlDbType.VarChar),       
new SqlParameter("@report_date" ,SqlDbType.DateTime),       
new SqlParameter("@union_code" ,SqlDbType.VarChar),        
new SqlParameter("@branch_code" ,SqlDbType.SmallInt),       
new SqlParameter("@report_tanto_name" ,SqlDbType.VarChar), 
new SqlParameter("@est_comp_clss",SqlDbType.VarChar),     
new SqlParameter("@cons_comp_name",SqlDbType.VarChar),    
new SqlParameter("@cons_comp_telno",SqlDbType.VarChar),  
new SqlParameter("@hall_seqno",SqlDbType.Int),       
new SqlParameter("@machine_name",SqlDbType.VarChar),      
new SqlParameter("@machine_qty",SqlDbType.Int),       
new SqlParameter("@est_date",SqlDbType.DateTime),          
new SqlParameter("@trader_reg_no",SqlDbType.VarChar),     
new SqlParameter("@chief_no",SqlDbType.VarChar),          
new SqlParameter("@chief_name",SqlDbType.VarChar),        
new SqlParameter("@worker1_name",SqlDbType.VarChar),      
new SqlParameter("@worker2_name",SqlDbType.VarChar),      
new SqlParameter("@worker3_name",SqlDbType.VarChar),      
new SqlParameter("@worker4_name",SqlDbType.VarChar),      
new SqlParameter("@worker5_name",SqlDbType.VarChar),      
new SqlParameter("@worker6_name",SqlDbType.VarChar),      
new SqlParameter("@fax_rcv_date",SqlDbType.DateTime),      
new SqlParameter("@ocr_pass_txt",SqlDbType.VarChar),     
new SqlParameter("@fax_error_flg",SqlDbType.VarChar), 
new SqlParameter("@dup_error_flg",SqlDbType.VarChar),     
new SqlParameter("@dif_report_flg",SqlDbType.VarChar),    
new SqlParameter("@report_sts",SqlDbType.VarChar),        
new SqlParameter("@reg_date",SqlDbType.DateTime),          
new SqlParameter("@upd_date",SqlDbType.DateTime),          
new SqlParameter("@upd_union_code",SqlDbType.VarChar),    
new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),   
new SqlParameter("@upd_func_id",SqlDbType.VarChar),       
new SqlParameter("@hall_name",SqlDbType.VarChar),         
new SqlParameter("@hall_pref_code",SqlDbType.SmallInt),    
new SqlParameter("@hall_city",SqlDbType.VarChar),         
new SqlParameter("@hall_address",SqlDbType.VarChar),      
new SqlParameter("@hall_tel",SqlDbType.VarChar )     
                                                };

sqlParameter[0].Value=model.ReportNo;
sqlParameter[1].Value=model.ReportClss;
sqlParameter[2].Value=model.ReportDate;
sqlParameter[3].Value=model.UnionCode;
sqlParameter[4].Value=model.BranchCode;
sqlParameter[5].Value=model.ReportTantoName;
sqlParameter[6].Value=model.EstCompClss;
sqlParameter[7].Value=model.ConsCompName;
sqlParameter[8].Value=model.ConsCompTelno;
sqlParameter[9].Value=model.HallSeqno;
sqlParameter[10].Value=model.MachineName;
sqlParameter[11].Value=model.MachineQty;
sqlParameter[12].Value=model.EstDate;
if (StringUtil.isEmpty(model.TraderRegNo))
{
    sqlParameter[13].Value = "";
}
else
{
    sqlParameter[13].Value = model.TraderRegNo.Substring(0, 2) + "-" + model.TraderRegNo.Substring(2, 5);
}
if (StringUtil.isEmpty(model.ChiefNo))
{
    sqlParameter[14].Value = "";
}
else
{
    sqlParameter[14].Value = model.ChiefNo.Substring(0, 2) + "-" + model.ChiefNo.Substring(2, 4);
}
//sqlParameter[13].Value=model.TraderRegNo.Substring(0,2)+"-"+model.TraderRegNo.Substring(2,5);
//sqlParameter[14].Value=model.ChiefNo.Substring(0,2)+"-"+model.ChiefNo.Substring(2,4);
sqlParameter[15].Value=model.ChiefName;
sqlParameter[16].Value=model.Worker1Name;
sqlParameter[17].Value=model.Worker2Name;
sqlParameter[18].Value=model.Worker3Name;
sqlParameter[19].Value=model.Worker4Name;
sqlParameter[20].Value=model.Worker5Name;
sqlParameter[21].Value=model.Worker6Name;

if (model.FaxRcvDate == null)
{
    sqlParameter[22].Value = DBNull.Value;
}
else
{
    sqlParameter[22].Value = model.FaxRcvDate;
}

sqlParameter[23].Value = model.OcrPassTxt == null ? "" : model.OcrPassTxt;
sqlParameter[24].Value = model.FaxErrorFlg == null ? "" : model.FaxErrorFlg;
sqlParameter[25].Value = model.DupErrorFlg == null ? "" : model.DupErrorFlg;
sqlParameter[26].Value = model.DifReportFlg == null ? "" : model.DifReportFlg;
sqlParameter[27].Value=model.ReportSts;
sqlParameter[28].Value=model.RegDate;
sqlParameter[29].Value=model.UpdDate;
sqlParameter[30].Value=model.UpdUnionCode;
sqlParameter[31].Value=model.UpdBranchCode;
sqlParameter[32].Value=model.UpdFuncId;
sqlParameter[33].Value=model.HallName;
sqlParameter[34].Value=model.HallPrefCode;
sqlParameter[35].Value=model.HallCity;
sqlParameter[36].Value=model.HallAddress;
sqlParameter[37].Value=model.HallTel;

               // SqlDB db=new SqlDB();
                db.ExecuteCmd(sql,sqlParameter);

		    } catch (Exception e) {
                LogManager.error(" trn_report_h_persister update "+e.Message+e.StackTrace);
                return false;
		    } 

		    return true;

	    }
        
        /**
	     * 指定された値のデータモデルを更新する。<br>
	     * 報告の承認処理
	     * @param con コネクション
	     * @param target TRN_Report_H
	     * @return boolean 
	     * @throws PersisterSQLException
	    */

	    public bool admission(SqlDB db, TrnReporth target){

		    
		    string sql = " update TRN_Report_H set " + " report_sts = @report_sts " + " , upd_date = @upd_date " + " , upd_union_code = @upd_union_code " + " , upd_branch_code = @upd_branch_code " + " , upd_func_id = @upd_func_id " + " where " + " report_no = @report_no ";

		    try {

                SqlParameter[] sqlParameter = {   
new SqlParameter("@report_sts",SqlDbType.VarChar),         
new SqlParameter("@upd_date",SqlDbType.DateTime),       
new SqlParameter("@upd_union_code" ,SqlDbType.VarChar),       
new SqlParameter("@upd_branch_code" ,SqlDbType.SmallInt), 
new SqlParameter("@upd_func_id" ,SqlDbType.VarChar),
new SqlParameter("@report_no" ,SqlDbType.VarChar)};
                sqlParameter[0].Value=TrnReporth.STATUS_CHECKED;
                sqlParameter[1].Value=target.UpdDate;
                sqlParameter[2].Value=target.UpdUnionCode;
                sqlParameter[3].Value=target.UpdBranchCode;
                sqlParameter[4].Value=target.UpdFuncId;
                sqlParameter[5].Value=target.ReportNo;

                db.ExecuteCmd(sql,sqlParameter);
		    } catch (Exception e) {
                LogManager.error("trn_report_h_persister admission "+e.Message+e.StackTrace);
                return false;
		    }

		    return true;

	    }

        public bool denial(TrnReporth target)
        {


            string sql = " update TRN_Report_H set " + " report_sts = @report_sts " + " , upd_date = @upd_date " + " , upd_union_code = @upd_union_code " + " , upd_branch_code = @upd_branch_code " + " , upd_func_id = @upd_func_id " + " where " + " report_no = @report_no ";
            SqlDB db = new SqlDB();
            try
            {

                SqlParameter[] sqlParameter = {   
new SqlParameter("@report_sts",SqlDbType.VarChar),         
new SqlParameter("@upd_date",SqlDbType.DateTime),       
new SqlParameter("@upd_union_code" ,SqlDbType.VarChar),       
new SqlParameter("@upd_branch_code" ,SqlDbType.SmallInt), 
new SqlParameter("@upd_func_id" ,SqlDbType.VarChar),
new SqlParameter("@report_no" ,SqlDbType.VarChar)};
                sqlParameter[0].Value = TrnReporth.STATUS_DENIAL;
                sqlParameter[1].Value = target.UpdDate;
                sqlParameter[2].Value = target.UpdUnionCode;
                sqlParameter[3].Value = target.UpdBranchCode;
                sqlParameter[4].Value = target.UpdFuncId;
                sqlParameter[5].Value = target.ReportNo;


                db.ExecuteCmd(sql, sqlParameter);
            }
            catch (Exception e)
            {
                LogManager.error("trn_report_h_persister admission " + e.Message + e.StackTrace);
                return false;
            }
            finally
            {
                db.Close();
            }

            return true;

        }

        /**
	     * TRN_Report_Hテーブルから検索によるホールリストを取得します。
	     * @param	con		Connection			DBコネクション
	     * @param	model 	ReportSearchModel		ホールサーチモデル
	    */

	    public Pager searchHall( ReportSearchModel model,int pageNo,int pageSize ) 
        {

		    ArrayList ret = new ArrayList();
            SqlDB db = new SqlDB();
            try
            {

                //SQL生成
                string pssql = "select distinct " + "hall_pref_code," + "hall_name," + "hall_city," + "hall_address," + "hall_tel" + " from mst_hall " + " where 1=1 ";
                LogManager.info("TRN_Report_H_Persister 組合員？オペレーター？事務局？ " + model.getUser_clss());


                if (!"K-999".Equals(model.getUnion_code())){
                    if ("10".Equals(model.getUser_clss()))
                    {

                        //組合員コード

                        pssql += " and (union_code = '" + model.getUnion_code() + "' or (union_code ='K-999' and branch_code = 0) )";

                    }
                    else
                    {
                        pssql += " and (union_code ='K-999' and branch_code = 0) ";
                    }
                }


                if (!StringUtil.isEmpty(model.getHall_name()))
                {

                    //ホール名

                    pssql += " and hall_name like '%" + model.getHall_name() + "%' ";

                }

                if (model.getHall_pref_code() > 0)
                {

                    //ホール所在地・都道府県

                    pssql += " and hall_pref_code = " + model.getHall_pref_code();

                }

                if (!StringUtil.isEmpty(model.getHall_city()))
                {

                    //ホール所在地・市区町村

                    pssql += " and hall_city like '%" + model.getHall_city() + "%' ";

                }

                if (!StringUtil.isEmpty(model.getHall_tel()))
                {

                    //ホール所在地・番地以降

                    pssql += " and hall_tel like '%" + model.getHall_tel() + "%' ";

                }

                pssql += " order by hall_name asc ";

                LogManager.info("TRN_Report_H_Persister search hall query : " + pssql);

                //検索ユーザが組合員の場合は組合員コードで縛る



                SqlDataReader dataReader = db.GetDataCmd(pssql, new SqlParameter[] { });

                while (dataReader.Read())
                {
                    TrnReporth report_h = new TrnReporth();
                    report_h.HallPrefCode = (short)dataReader["hall_pref_code"];
                    report_h.HallName = DBNull.Value==dataReader["hall_name"] ?"":(string)dataReader["hall_name"];
                    report_h.HallCity = DBNull.Value == dataReader["hall_city"] ? "" : (string)dataReader["hall_city"];
                    report_h.HallAddress = DBNull.Value == dataReader["hall_address"] ? "" : (string)dataReader["hall_address"];
                    report_h.HallTel = DBNull.Value == dataReader["hall_tel"] ? "" : (string)dataReader["hall_tel"];

                    ret.Add(report_h);

                }


            }
            catch (Exception e)
            {
                LogManager.error("trn_report_h_persister " + e.Message + e.StackTrace);
            }
            finally
            {
                db.Close();
            }

            if (pageNo == 0)
            {
                pageNo = 1;
            }

            Pager pager = new Pager();
            pager.PageNo = pageNo;
            pager.PageSize = pageSize;
            pager.Total = ret.Count;
            pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
            ArrayList result = new ArrayList();
            
            if (ret.Count == 1)
            {
                pager.List = ret;
               
            }
            else
            {
                for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= ret.Count ? ret.Count : ((pageNo - 1) * pageSize + pageSize)); i++)
                {
                    result.Add((TrnReporth)ret[i]);
                   
                }
                pager.List = result;
            }

            return pager;

	    }

        
        /**	     * 未承認の報告見出の数を取得する。	     * @param con コネクション	     * @param uion_code 組合員コード	     * @return int 未承認の報告見出の数	    */	    public int getUnadmissionReport(SqlDB db, string union_code) {		    int ret = -1;            LogManager.info( "TRN_Report_H_Persister getUnadmissionReport --- start! " );		    String pssql = "select count(report_no) from trn_report_h where report_sts = @report_sts and union_code = @union_code ";

            SqlDataReader dataReader = null;		    try {                SqlParameter[] sqlParameter = {   new SqlParameter("@report_sts", SqlDbType.VarChar),
                                                  new SqlParameter("@union_code", SqlDbType.VarChar)
                                                 
                                              };

                sqlParameter[0].Value = TrnReporth.STATUS_UNCHECK;
                sqlParameter[1].Value = union_code;
                dataReader = db.GetDataCmd(pssql, sqlParameter);
                
                if (dataReader.Read())
                {
                    ret = (int)dataReader[0];
                }			                   LogManager.info(" TRN_Report_H_Persister getUnadmissionReport  --- ret : "+ret);			    		    } catch (Exception exe) {
                LogManager.error(" TRN_Report_H_Persister getUnadmissionReport  --- ret : " + exe.Message);		    } finally {                try {
                    if (dataReader != null)
                    {
                        dataReader.Close();
                    }			    } catch (Exception exee) {

                    LogManager.error(" TRN_Report_H_Persister getUnadmissionReport  --- close : " + exee.Message+exee.StackTrace);				    			    }		    }		    return ret;	    }
    
    }
}
